package com.service.lims;

import java.io.IOException;
import java.sql.SQLException;
import java.util.*;

import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSON;
import com.common.db.DBDruidUtils;
import com.common.spring.SpringMvcUtils;
import com.enums.BtnDbPermissionEnum;
import com.model.sys.SysUser;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.excel.EasyExcel;
import com.common.StringUtils;
import com.common.easyui.EasyuiUtils;
import com.common.http.HttpResult;
import com.common.http.HttpStatus;
import com.dao.lims.LimsBroadcastsDao;
import com.model.lims.LimsBroadcastsModel;
import com.common.model.Global;
import org.springframework.util.CollectionUtils;

@Service
public class LimsBroadcastsService {
    static final Logger logger = LoggerFactory.getLogger(LimsBroadcastsService.class);

    @Autowired
    LimsBroadcastsDao limsBroadcastsDao;

    /**
     * 列表查询
     *
     * @param queryMap
     * @return
     */
    public HttpResult selectList(Map<String, Object> queryMap) {
        HttpResult httpResult = new HttpResult();
        EasyuiUtils.addPageNum(queryMap);
        EasyuiUtils.addFilter(queryMap);
        List<LimsBroadcastsModel> list = limsBroadcastsDao.selectList(queryMap);
        int count = limsBroadcastsDao.selectListCount(queryMap);
        httpResult.setRows(list);
        httpResult.setTotal(count);
        logger.info("selectList:{}", list);
        return httpResult;
    }

    /**
     * 详情
     *
     * @param
     * @return
     */
    public HttpResult selectInfor(String spId) {
        HttpResult httpResult = new HttpResult();
        LimsBroadcastsModel limsBroadcastsModel = limsBroadcastsDao.load(spId);
        httpResult.setData(limsBroadcastsModel);
        httpResult.setCode(HttpStatus.SUCCESS_CODE);
        return httpResult;
    }

    /**
     * 保存
     *
     * @param
     * @return
     */
    public HttpResult save(LimsBroadcastsModel limsBroadcastsModel) {
        if (limsBroadcastsModel == null) {
            return HttpResult.error("参数不能为空");
        }
        HttpResult httpResult = new HttpResult();
        int result = 0;
        if (!StringUtils.isNotNull(limsBroadcastsModel.getSpId())) {
            limsBroadcastsModel.setSpId(UUID.randomUUID().toString());
            result = limsBroadcastsDao.insert(limsBroadcastsModel);
        } else {
            result = limsBroadcastsDao.update(limsBroadcastsModel);
        }
        if (result > 0) {
            httpResult.setMsg("保存成功!");
            httpResult.setData(limsBroadcastsModel);
        } else {
            httpResult.setCode(HttpStatus.GLOBAL_EXCEPTION_CODE);
            httpResult.setMsg("保存失败!");
        }
        return httpResult;
    }


    /**
     * 删除
     *
     * @param
     * @return
     */
    public HttpResult delete(String spId) {
        if (!StringUtils.isNotNull(spId)) {
            return HttpResult.error("参数不能为空");
        }
        LimsBroadcastsModel model = limsBroadcastsDao.load(spId);

        HttpResult httpResult = new HttpResult();
        int result = limsBroadcastsDao.delete(spId);
        if (result > 0) {
            httpResult.setMsg("删除成功！");
        } else {
            httpResult.setCode(HttpStatus.GLOBAL_EXCEPTION_CODE);
            httpResult.setMsg("不存在此条数据！");
        }
        return httpResult;
    }


    /**
     * 导出
     *
     * @param queryMap
     * @param response
     */
    public void export(Map<String, Object> queryMap, HttpServletResponse response) {
        List<LimsBroadcastsModel> list = limsBroadcastsDao.selectList(queryMap);
        logger.info("export:{}", list);
        try {
            EasyExcel.write(response.getOutputStream(), LimsBroadcastsModel.class).sheet("模板").doWrite(list);
        } catch (IOException e) {
            logger.error("导出错误:{}", e.getMessage());
        }
    }

    /**
     * 查询接收后40min内未提交的样品数据，并保存至数据库
     *
     * @param params
     */
    public HttpResult getLimsNoCommitSamples(Map params) {
        HttpResult httpResult = new HttpResult();
        SysUser sysUser = SpringMvcUtils.getSysUser();
        String dept = "";
        if (null == sysUser) {
            return HttpResult.error("请先登录");
        }
        if (sysUser.getBtnPermission() != null) {
            if (sysUser.getBtnPermission().contains(BtnDbPermissionEnum.xyLimsSamplesBroadcast.getValue())) {
                dept = "新源公司";
            } else if (sysUser.getBtnPermission().contains(BtnDbPermissionEnum.spLimsSamplesBroadcast.getValue())) {
                dept = "思派公司";
            } else {
                return HttpResult.error("请先联系管理员配置按钮权限");
            }
        }

        List<LimsBroadcastsModel> samples = new ArrayList<>();
        // 获取数据库连接
        DBDruidUtils dbh = new DBDruidUtils("lims", "config_prod/db_lims.properties");
        QueryRunner runner = dbh.getRunner();
        MapListHandler handler = new MapListHandler();
        String noCommitSamplesSql = "SELECT to_char( ORDERS.RECDATE, 'yyyy-MM-dd hh24:mi:ss' ) recDate,ORDERS.ORDNO samNo,ORDERS.MATCODE  samName FROM ORDERS\n" +
                "\tLEFT JOIN FOLDERS ON FOLDERS.FOLDERNO = ORDERS.FOLDERNO \n" +
                "\tLEFT JOIN BATCHES ON BATCHES.BATCHID = FOLDERS.BATCHID \n" +
                "WHERE\n" +
                "\tORDERS.RECDATE < (SYSDATE - (40 / 24 / 60)) \n" +
                "\tAND ORDERS.STATUS IN ( 'Logged', 'Active' ) \n" +
                "\tAND ORDERS.APPRDATE IS NULL \n" +
                //"\tAND TO_CHAR( ORDERS.RECDATE, 'YYYY-MM-DD' ) = TO_CHAR( SYSDATE, 'YYYY-MM-DD' )\n" +
                "\tAND FOLDERS.DEPT = ? \n" +
                "\tAND EXISTS (\n" +
                "\tSELECT\n" +
                "\t\t1 \n" +
                "\tFROM\n" +
                "\t\tORDTASK \n" +
                "\tWHERE\n" +
                "\t\tTS = 'Logged' \n" +
                "\tAND TSA IS NULL \n" +
                "\tAND ORDNO = ORDERS.ORDNO)\n" +
                "and exists(select 1 \n" +
                "from IPSAMPLEGROUPDETAILS \n" +
                "where IPSAMPLEGROUPDETAILS.ISBROADCAST = 'Y'\n" +
                "AND IPSAMPLEGROUPDETAILS.SP_CODE = ORDERS.SP_CODE\n" +
                "AND IPSAMPLEGROUPDETAILS.SAMPLEGROUPCODE = BATCHES.SAMPLEGROUPCODE)" +
                "\tORDER BY ORDERS.RECDATE";

        try {
            List<Map<String, Object>> list = runner.query(noCommitSamplesSql, handler, dept);
            logger.info(JSON.toJSONString(list));
            if (CollectionUtils.isEmpty(list)) {
                httpResult.setRows(list);
                return httpResult;
            }

            // 将数据保存至CMP数据库
            for (Map result : list) {
                LimsBroadcastsModel limsBroadcastsModel = new LimsBroadcastsModel();
                String samNo = String.valueOf(result.get("samNo"));
                // 根据样品编号查询数据库中是否已存在
                LimsBroadcastsModel samTemp = limsBroadcastsDao.selectBySamNo(samNo);
                // 不存在则insert
                if (null == samTemp) {
                    limsBroadcastsModel.setSpId(UUID.randomUUID().toString());
                    // 未提交
                    limsBroadcastsModel.setSamStatus("0");
                    // 样品编号
                    limsBroadcastsModel.setSamNo(samNo);
                    // 样品名称
                    limsBroadcastsModel.setSamName(String.valueOf(result.get("samName")));
                    // 接收时间
                    limsBroadcastsModel.setRecDate(String.valueOf(result.get("recDate")));
                    // 未提交播报次数
                    limsBroadcastsModel.setRecBroadcastNum(0);
                    // 未打印播报次数
                    limsBroadcastsModel.setPusNoPrintNum(0);
                    // 已提交播报次数
                    limsBroadcastsModel.setSubBroadcast(0);
                    limsBroadcastsModel.setBroadcastType("noCommit");
                    limsBroadcastsDao.insert(limsBroadcastsModel);
                    samples.add(limsBroadcastsModel);
                } else {
                    //   if (samTemp.getRecBroadcastNum() < 7) {
                    samTemp.setBroadcastType("noCommit");
                    samples.add(samTemp);
                    //   }
                }

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        httpResult.setRows(samples);
        return httpResult;
    }

    /**
     * 超时未提交的样品播报次数+1，超七次后不再提醒
     *
     * @param params
     * @return
     */
    public HttpResult updateBroadcastNumBySamNo(Map params) {
        String samNo = String.valueOf(params.get("samNo"));
        if (StringUtils.isNullOrEmpty(samNo)) {
            return HttpResult.error("样品编号不得为空");
        }
        LimsBroadcastsModel samInfo = limsBroadcastsDao.selectBySamNo(samNo);
        int num = samInfo.getRecBroadcastNum();
        // 未提交的样品提醒达七次后，不在提醒
        // if (num < 7) {
        samInfo.setRecBroadcastNum(num + 1);
        limsBroadcastsDao.update(samInfo);
        //  }
        return HttpResult.ok();
    }

    /**
     * 查询当天发布后20min内未打印COA的样品数据，并保存至数据库
     *
     * @param params
     */
    public HttpResult getLimsNoPrintCOASamples(Map params) {
        HttpResult httpResult = new HttpResult();
        SysUser sysUser = SpringMvcUtils.getSysUser();
        String dept = "";
        if (null == sysUser) {
            return HttpResult.error("请先登录");
        }
        if (sysUser.getBtnPermission() != null) {
            if (sysUser.getBtnPermission().contains(BtnDbPermissionEnum.xyLimsSamplesBroadcast.getValue())) {
                dept = "新源公司";
            } else if (sysUser.getBtnPermission().contains(BtnDbPermissionEnum.spLimsSamplesBroadcast.getValue())) {
                dept = "思派公司";
            } else {
                return HttpResult.error("请先联系管理员配置按钮权限");
            }
        }
        List<LimsBroadcastsModel> samples = new ArrayList<>();
        // 获取数据库连接
        DBDruidUtils dbh = new DBDruidUtils("lims", "config_prod/db_lims.properties");
        QueryRunner runner = dbh.getRunner();
        MapListHandler handler = new MapListHandler();
        String noPrintSamplesSql = "SELECT\n" +
                "\tORDERS.ORDNO samNo,\n" +
                "\tORDERS.MATCODE samName,\n" +
                "\tFOLDERS.DEPT,\n" +
                "\tORDERS.APPRDATE subDate,\n" +
                "\tBATCHES.CLOSEDATE || ' ' || BATCHES.CLOSETIME pubDATE \n" +
                "FROM\n" +
                "\tORDERS\n" +
                "\tLEFT JOIN FOLDERS ON FOLDERS.FOLDERNO = ORDERS.ORDNO\n" +
                "\tLEFT JOIN BATCHES ON BATCHES.BATCHID = FOLDERS.BATCHID \n" +
                "WHERE\n" +
                "\tBATCHES.batchstatus = 'Released' \n" +
                "\tAND ORDERS.APPRSTS = 'Released' \n" +
                "\tAND ORDERS.APPRSTS IS NOT NULL \n" +
                "\tAND NOT EXISTS ( SELECT 1 FROM REPORTS_HISTORY WHERE ORDNO = ORDERS.ORDNO ) \n" +
                "\tAND BATCHES.CLOSETIME < TO_CHAR(( SYSDATE - ( 20 / 24 / 60 )), 'HH24:MI:SS' ) \n" +
               // "\tAND TO_CHAR( BATCHES.CLOSEDATE, 'YYYY-MM-DD' ) = TO_CHAR( SYSDATE, 'YYYY-MM-DD' )\n" +
                "\tAND EXISTS(SELECT 1 \n" +
                "FROM\n" +
                "\tIPSAMPLEGROUPDETAILS \n" +
                "WHERE\n" +
                "\tIPSAMPLEGROUPDETAILS.ISBROADCAST = 'Y' \n" +
                "\tAND IPSAMPLEGROUPDETAILS.SP_CODE = ORDERS.SP_CODE \n" +
                "\tAND IPSAMPLEGROUPDETAILS.SAMPLEGROUPCODE = BATCHES.SAMPLEGROUPCODE) " +
                "\tAND FOLDERS.DEPT=?";

        try {
            List<Map<String, Object>> list = runner.query(noPrintSamplesSql, handler, dept);
            logger.info(JSON.toJSONString(list));
            if (CollectionUtils.isEmpty(list)) {
                httpResult.setRows(list);
                return httpResult;
            }

            // 将数据保存至CMP数据库
            for (Map result : list) {
                LimsBroadcastsModel limsBroadcastsModel = new LimsBroadcastsModel();
                String samNo = String.valueOf(result.get("samNo"));
                // 根据样品编号查询数据库中是否已存在
                LimsBroadcastsModel samTemp = limsBroadcastsDao.selectBySamNo(samNo);
                // 不存在则insert
                if (null == samTemp) {
                    limsBroadcastsModel.setSpId(UUID.randomUUID().toString());
                    // 样品编号
                    limsBroadcastsModel.setSamNo(samNo);
                    // 样品名称
                    limsBroadcastsModel.setSamName(String.valueOf(result.get("samName")));
                    // 发布时间
                    limsBroadcastsModel.setPusDate(String.valueOf(result.get("pubDate")));
                    // 已提交的样品，将未提交提醒次数设置为7（不再提醒已接收未提交）
                    //  limsBroadcastsModel.setRecBroadcastNum(7);
                    // 提交时间
                    limsBroadcastsModel.setSubDate(String.valueOf(result.get("subDate")));
                    // 未提交播报次数
                    limsBroadcastsModel.setRecBroadcastNum(0);
                    // 未打印播报次数
                    limsBroadcastsModel.setPusNoPrintNum(0);
                    // 已提交播报次数
                    limsBroadcastsModel.setSubBroadcast(0);
                    limsBroadcastsModel.setBroadcastType("noPrint");
                    limsBroadcastsDao.insert(limsBroadcastsModel);
                    samples.add(limsBroadcastsModel);
                } else {
                    // 未打印
                    samTemp.setSamStatus("2");
                    samTemp.setBroadcastType("noPrint");
                    // 已提交的样品，将未提交提醒次数设置为7（不再提醒已接收未提交）
                    //   samTemp.setRecBroadcastNum(7);
                    limsBroadcastsDao.update(samTemp);
                    // 超过3次后，不再提醒
                    //     if (samTemp.getPusNoPrintNum() < 3) {
                    samples.add(samTemp);
                    //     }
                }

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        httpResult.setRows(samples);
        return httpResult;
    }

    /**
     * 超时未打印的样品播报次数+1，超3次后不再提醒
     *
     * @param params
     * @return
     */
    public HttpResult updateNoPrintBroadcastNumBySamNo(Map params) {
        String samNo = String.valueOf(params.get("samNo"));
        if (StringUtils.isNullOrEmpty(samNo)) {
            return HttpResult.error("样品编号不得为空");
        }
        LimsBroadcastsModel samInfo = limsBroadcastsDao.selectBySamNo(samNo);
        int num = samInfo.getRecBroadcastNum();
        // 未提交的样品提醒达3次后，不在提醒
        // if (num < 3) {
        samInfo.setPusNoPrintNum(num + 1);
        limsBroadcastsDao.update(samInfo);
        //  }
        return HttpResult.ok();
    }


    /**
     * 查询当天已提交未审核的样品数据，并保存至数据库
     *
     * @param params
     */
    public HttpResult getLimsCommitedSamples(Map params) {
        HttpResult httpResult = new HttpResult();
        SysUser sysUser = SpringMvcUtils.getSysUser();
        String dept = "";
        if (null == sysUser) {
            return HttpResult.error("请先登录");
        }
        if (sysUser.getBtnPermission() != null) {
            if (sysUser.getBtnPermission().contains(BtnDbPermissionEnum.xyLimsSamplesBroadcast.getValue())) {
                dept = "新源公司";
            } else if (sysUser.getBtnPermission().contains(BtnDbPermissionEnum.spLimsSamplesBroadcast.getValue())) {
                dept = "思派公司";
            } else {
                return HttpResult.error("请先联系管理员配置按钮权限");
            }
        }
        List<LimsBroadcastsModel> samples = new ArrayList<>();
        // 获取数据库连接
        DBDruidUtils dbh = new DBDruidUtils("lims", "config_prod/db_lims.properties");
        QueryRunner runner = dbh.getRunner();
        MapListHandler handler = new MapListHandler();
        String commitedSamplesSql = "SELECT\n" +
                "\tORDERS.ORDNO samNo,\n" +
                "\tORDERS.MATCODE samName,\n" +
                "\tORDERS.SAMPDATE subDate\n" +
                "FROM\n" +
                "\tORDERS \n" +
                "\tLEFT JOIN FOLDERS ON FOLDERS.FOLDERNO = ORDERS.FOLDERNO \n" +
                "\tLEFT JOIN BATCHES ON BATCHES.BATCHID = FOLDERS.BATCHID \n" +
                "WHERE\n" +
                "\tORDERS.APPRSTS IS  NULL \n" +
              //  "\tAND TO_CHAR( ORDERS.SAMPDATE, 'YYYY-MM-DD' ) = TO_CHAR( SYSDATE, 'YYYY-MM-DD' ) \n" +
                "\tAND EXISTS(SELECT 1 \n" +
                "FROM\n" +
                "\tIPSAMPLEGROUPDETAILS \n" +
                "WHERE\n" +
                "\tIPSAMPLEGROUPDETAILS.ISBROADCAST = 'Y' \n" +
                "\tAND IPSAMPLEGROUPDETAILS.SP_CODE = ORDERS.SP_CODE \n" +
                "\tAND IPSAMPLEGROUPDETAILS.SAMPLEGROUPCODE = BATCHES.SAMPLEGROUPCODE) \n"+
                "AND NOT EXISTS ( SELECT 1 FROM ORDTASK WHERE TS = 'Logged' AND TSA IS NULL AND ORDNO = ORDERS.ORDNO )"+
                "\tAND FOLDERS.DEPT = ?  order by ORDERS.SAMPDATE";

        try {
            List<Map<String, Object>> list = runner.query(commitedSamplesSql, handler, dept);
            logger.info(JSON.toJSONString(list));
            if (CollectionUtils.isEmpty(list)) {
                httpResult.setRows(list);
                return httpResult;
            }

            // 将数据保存至CMP数据库
            for (Map result : list) {
                LimsBroadcastsModel limsBroadcastsModel = new LimsBroadcastsModel();
                String samNo = String.valueOf(result.get("samNo"));
                // 根据样品编号查询数据库中是否已存在
                LimsBroadcastsModel samTemp = limsBroadcastsDao.selectBySamNo(samNo);
                // 不存在则insert
                if (null == samTemp) {
                    limsBroadcastsModel.setSpId(UUID.randomUUID().toString());
                    // 已提交
                    limsBroadcastsModel.setSamStatus("1");
                    // 样品编号
                    limsBroadcastsModel.setSamNo(samNo);
                    // 样品名称
                    limsBroadcastsModel.setSamName(String.valueOf(result.get("samName")));
                    // 已提交的样品，将未提交提醒次数设置为7（不再提醒已接收未提交）
                    //  limsBroadcastsModel.setRecBroadcastNum(7);
                    // 提交时间
                    limsBroadcastsModel.setSubDate(String.valueOf(result.get("subDate")));
                    // 未提交播报次数
                    limsBroadcastsModel.setRecBroadcastNum(0);
                    // 未打印播报次数
                    limsBroadcastsModel.setPusNoPrintNum(0);
                    // 已提交播报次数
                    limsBroadcastsModel.setSubBroadcast(0);
                    limsBroadcastsModel.setBroadcastType("commited");
                    limsBroadcastsDao.insert(limsBroadcastsModel);
                    samples.add(limsBroadcastsModel);
                } else {
                    // 已提交
                    samTemp.setSamStatus("1");
                    samTemp.setBroadcastType("commited");
                    // 已提交的样品，将未提交提醒次数设置为7（不再提醒已接收未提交）
                    //  samTemp.setRecBroadcastNum(7);
                    limsBroadcastsDao.update(samTemp);

                    // 超过3次后，不再提醒
                    //  if (samTemp.getSubBroadcast() < 3) {
                    samples.add(samTemp);
                    //  }
                }

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        httpResult.setRows(samples);
        return httpResult;
    }

    /**
     * 已提交播报次数+1，超3次后不再提醒
     *
     * @param params
     * @return
     */
    public HttpResult updateCommitedNumBySamNo(Map params) {
        String samNo = String.valueOf(params.get("samNo"));
        if (StringUtils.isNullOrEmpty(samNo)) {
            return HttpResult.error("样品编号不得为空");
        }
        LimsBroadcastsModel samInfo = limsBroadcastsDao.selectBySamNo(samNo);
        int num = samInfo.getSubBroadcast();
        // 未提交的样品提醒达3次后，不在提醒
        //   if (num < 3) {
        samInfo.setSubBroadcast(num + 1);
        limsBroadcastsDao.update(samInfo);
        //   }
        return HttpResult.ok();
    }
}
